"""
Case Type   : fullsql
Case Name   : ttrack_stmt_retention_time参数gs_guc reload异常输入校验和set异常输入校验
Create At   : 2022/02/07
Owner       : zhao-pengcheng-g
Description :
    1.设置为'0,0'，查看参数
    2.设置为86400,604800，查看参数
    3.设置为-1,0，查看参数
    4.设置为0,-1，查看参数
    5.设置为864000,0，查看参数
    6.设置为0,6048000000000，查看参数
    7.设置为带单位的值，查看参数
    8.设置为字母，查看参数
    9.设置为特殊字符，查看参数
    10.设置分隔符为";"，查看参数
    11.track_stmt_retention_time参数set异常输入校验
    12.恢复参数
Expect      :
    1.设置为'0,0'成功，查看参数为'0,0'
    2.设置为86400,604800成功，查看参数为'86400,60400'
    3.设置为-1,0失败，查看参数为'86400,60400'
    4.设置为0,-1失败，查看参数为'86400,60400'
    5.设置为864000,0失败，查看参数为'86400,60400'
    6.设置为0,6048000000000失败，查看参数为'86400,60400'
    7.设置为带单位的值失败，查看参数为('86400,60400'
    8.设置为字母失败，查看参数为'86400,60400'
    9.设置为特殊字符失败，合理报错：The parameter('0#,**')
    exists illegal character:#，查看参数为 '86400,60400'
    10.设置分隔符为";"，查看参数为'86400,60400'
    11.设置失败，提示：parameter "track_stmt_retention_time" cannot be changed now
    12.恢复参数成功
History     :
"""

import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0029 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()

    def test_fullsql(self):
        text = "---step1:设置为'0,0'，查看参数'," \
               "expect:设置成功,查看参数结果为'0,0'---"
        self.logger.info(text)
        self.config_time = 'track_stmt_retention_time'
        self.show_time_cmd = f'show {self.config_time};'
        check_time = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(check_time)
        self.time_default = check_time.splitlines()[-2].strip()
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='0,0'")
        self.assertTrue(guc_time, '执行失败:' + text)
        check_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(check_time_msg)
        self.common.equal_sql_mdg(check_time_msg, self.config_time,
                                  '0,0', '(1 row)', flag='1')

        text = "---step2:设置为86400,604800，查看参数," \
               "expect:设置成功,查看参数结果为'86400,60400'---"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='86400,60400'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step3:设置为'-1,0'，查看参数,expect:设置失败，" \
               "查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='-1,0'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step4:设置为'0,-1'，查看参数,expect:设置失败，" \
               "查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='0,-1'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step5:设置为'864000,0'，查看参数,expect:设置失败，" \
               "查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='864000,0'")
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step6:设置为'0,6048000000000'，查看参数," \
               "expect:设置失败，查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='0,6048000000000'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step7:设置为带单位的值，查看参数," \
               "expect:设置失败，查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='5s,5s'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step8:设置为字母，查看参数," \
               "expect:设置失败，查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='a,a'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step9:设置为特殊字符，查看参数," \
               "expect:设置失败，查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='$##,**'",
                                               get_detail=True)
        self.assertIn("The parameter('0#,**') exists illegal character:#",
                      guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step10:设置分隔符为';'，查看参数," \
               "expect:设置失败，查看参数结果为'86400,60400'"
        self.logger.info(text)
        guc_time = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               f"{self.config_time}='10;10'")
        self.assertTrue(guc_time, '执行失败:' + text)
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  '86400,60400', '(1 row)', flag='1')

        text = "---step11:track_stmt_retention_time参数set异常输入校验," \
               "expect:设置失败---"
        self.logger.info(text)
        sql_cmd = f"set track_stmt_retention_time='0,0';" \
                  f"show track_stmt_retention_time;"
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertIn(f"parameter \"{self.config_time}\" "
                      f"cannot be changed now" and '86400,60400',
                      sql_msg, '执行失败:' + text)

    def tearDown(self):
        text = '---step12:恢复参数，expect:成功---'
        self.logger.info(text)
        check_time = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(check_time)
        current = check_time.splitlines()[-2].strip()
        if self.time_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f"{self.config_time}='{self.time_default}'")
            self.assertTrue(result, '参数恢复失败')
        show_time_msg = self.sh_primy.execut_db_sql(self.show_time_cmd)
        self.logger.info(show_time_msg)
        self.common.equal_sql_mdg(show_time_msg, self.config_time,
                                  self.time_default, '(1 row)', flag='1')
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0029 finish---')
